using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Configuration;
using System.Globalization;
using System.IO;
using System.Collections.Generic;
using System.Collections;
using System.Linq;

namespace Web.Ajax.Data
{
    /// <summary>
    /// Provides static easy to use functions for Accessing and modifying a DataBase 
    /// using the SqlClient provider. <br>
    /// Expects the Database Connection string to be in the Web.Config appSettings 
    /// with the name <b>Database</b>. <br>
    /// e.g. &lt;add key="Database" value="server=#Server;uid=#UserName;pwd=#Password;database=#DatabaseName" /&gt;<br><br>
    /// To include optional error logging include the url of the log file in the Web.Config 
    /// appSettings with the name <b>SqlErrorLog</b>.  
    /// </summary>
    public class Database
    { 
        #region ScriptBatch Class
        /// <summary>
        /// This class can be used when you have a very large Database Script to run. 
        /// It is typically used as follows:<br><br>
        /// <code>
        /// ScriptBatch script=new ScriptBatch();<br>
        /// for(...)<br>
        /// {<br>
        ///		//Add Sql Commands<br>
        ///		script.Append("Sql Commands");<br>
        /// }<br>
        /// script.Flush();<br>
        /// </code>
        /// </summary>
        public class ScriptBatch
        {
            private StringBuilder script = new StringBuilder();

            #region WhenExceedsSendLength
            /// <summary>
            /// The <b>WhenExceedsSendLength</b> property is the script length, that once exceeded
            /// sends the script to the DataBase Server on subsequent calls to Append. 
            /// </summary>
            public static int WhenExceedsSendLength
            {
                get
                {
                    string s = ConfigurationManager.AppSettings["ScriptBatchSize"];
                    if (s != null)
                    {
                        try
                        {
                            int i = int.Parse(s);
                        }
                        catch (Exception e)
                        {
                            LogError("InvalidBatchSize (Are you sure it's an int?), " + e.Message, new StringBuilder("n/a"));
                        }
                    }
                    return 10000;
                }
            }
            #endregion

            public Database Database;

            public ScriptBatch(Database Database)
            {
                this.Database = Database;
            }

            private int RunScriptIfLengthExceded()
            {
                int NumberOfRowsEffected = 0;
                if (script.Length > WhenExceedsSendLength)
                {
                    NumberOfRowsEffected = Database.RunScript(script);
                    script = new StringBuilder();
                }
                return NumberOfRowsEffected;
            }

            public int Append(string _script)
            {
                script.Append(_script);
                return RunScriptIfLengthExceded();
            }

            public int Append(StringBuilder _script)
            {
                script.Append(_script);
                return RunScriptIfLengthExceded();
            }

            public int Flush()
            {
                int NumberOfRowsEffected = 0;
                if (script.Length > 0)
                {
                    NumberOfRowsEffected = Database.RunScript(script);
                    script = new StringBuilder();
                }
                return NumberOfRowsEffected;
            }
        }
        #endregion

        #region Database
        public Database(string ConnectionString)
        {
            var c=System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionString];
            if (c != null)
                this.connectionString = c.ToString();
            else
                this.connectionString = ConnectionString;
        }
        #endregion

        #region ConnectionString
        private string connectionString;
        public string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }
        #endregion

        #region ErrorLogging
        #region SqlErrorFile
        public static string SqlErrorFile
        {
            get
            {
                string url = ConfigurationManager.AppSettings["SqlErrorLog"];
                //if (url != null)
                //   return System.Web.HttpContext.Current.Server.MapPath(url);
                return null;
            }
        }
        #endregion

        #region LogError
        public static void LogError(string message, StringBuilder script)
        {
            message = string.Format("Error Message from database: Message is : {0}. Script is : {1}", message, script);
        }
        #endregion
        #endregion

        #region Sql Methods
        #region Select
        #region Vanilla
        public bool Select(StringBuilder query, DataTable DataTable)
        {
            bool ret = true;
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd = conn.CreateCommand();
            cmd.CommandText = query.ToString();
            SqlDataAdapter dataAdapter = null;
            try
            {
                cmd.Connection = conn;
                dataAdapter = new SqlDataAdapter();
                dataAdapter.SelectCommand = cmd;
                dataAdapter.Fill(DataTable);
            }
            catch (Exception e)
            {
                LogError(e.Message, query);
                throw e;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return ret;
        }
        #endregion

        #region DataTable
        public bool Select(DataTable Table)
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT * FROM #TN;");
            query.Replace("#TN", Table.TableName);
            return Select(query, Table);
        }
        #endregion

        #region DataTable
        public bool SelectAndOrder(DataTable Table, string OrderBy)
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT * FROM #TN ORDER BY #OB;");
            query.Replace("#TN", Table.TableName);
            query.Replace("#OB", OrderBy);
            return Select(query, Table);
        }
        #endregion

        #region TableName, WhereClause, DataTable
        public bool Select(string TableName, string WhereClause, DataTable DataTable)
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT * FROM ");
            query.Append(TableName);
            query.Append(" WHERE ");
            query.Append(WhereClause);
            return Select(query, DataTable);
        }
        #endregion

        #region WhereClause, DataTable
        public bool Select(string WhereClause, DataTable DataTable)
        {
            return Select(DataTable.TableName, WhereClause, DataTable);
        }
        #endregion

        #region DataTable, PrimaryKeyValue
        public bool Select(DataTable DataTable, string PrimaryKeyValue)
        {
            DataColumn[] cols = DataTable.PrimaryKey;
            if (cols.Length != 1)
                return false;
            DataColumn col = cols[0];
            StringBuilder WhereClause = new StringBuilder();
            WhereClause.Append(col.ColumnName);
            WhereClause.Append("=#VA");
            if (col.DataType.Name == "String" || col.DataType.Name == "DateTime")
            {
                WhereClause.Replace("#VA", "'#VA'");
            }
            WhereClause.Replace("#VA", PrimaryKeyValue);
            return Select(DataTable.TableName, WhereClause.ToString(), DataTable);
        }
        #endregion
        #endregion

        #region RunScript
        /// <summary>
        /// Executes a non query. Returns the number of rows affected. Returns -1 if there was an error.
        /// </summary>
        /// <param name="script"></param>
        /// <returns></returns>
        public int RunScript(StringBuilder script)
        {
            if (script == null || script.Length == 0)
                return -1;
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SET DATEFORMAT mdy;\n " + script.ToString();
			cmd.CommandTimeout = 0;
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                LogError(e.Message, script);
                throw e;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        #endregion

        #region Insert
        /// <summary>
        /// Returns the Number of rows effected.
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="columns"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public int Insert(string TableName, string columns, string values)
        {
            StringBuilder insert = new StringBuilder();
            insert.Append("INSERT INTO ");
            insert.Append(TableName);
            insert.Append(" ");
            insert.Append(columns);
            insert.Append(" VALUES ");
            insert.Append(values);
            return Insert(insert);
        }

        /// <summary>
        /// Returns the number of rows effected.
        /// </summary>
        /// <param name="insert"></param>
        /// <returns></returns>
        public int Insert(StringBuilder insert)
        {
            return RunScript(insert);
        }
        #endregion

        #region Update
        #region Update StringBuilder
        /// <summary>
        /// Returns the number of rows effected.
        /// </summary>
        /// <param name="update"></param>
        /// <returns></returns>
        public int Update(StringBuilder update)
        {
            return RunScript(update);
        }
        #endregion

        #region TableName, Set, WhereClause
        /// <summary>
        /// Returns the number of rows effected.
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Set"></param>
        /// <param name="WhereClause"></param>
        /// <returns></returns>
        public int Update(string TableName, string Set, string WhereClause)
        {
            StringBuilder update = new StringBuilder();
            update.Append("UPDATE ");
            update.Append(TableName);
            update.Append(" SET ");
            update.Append(Set);
            update.Append(" WHERE ");
            update.Append(WhereClause);
            return Update(update);
        }
        #endregion
        #endregion

        #region Delete
        /// <summary>
        /// Returns the number of rows effected.
        /// </summary>
        /// <param name="delete"></param>
        /// <returns></returns>
        public int Delete(StringBuilder delete)
        {
            return RunScript(delete);
        }

        /// <summary>
        /// Returns the number of rows effected.
        /// </summary>
        /// <param name="DataRow"></param>
        /// <returns></returns>
        public Exception Delete(DataRow DataRow)
        {
            StringBuilder WhereClause = new StringBuilder("#WH");
            BuildWhereString(DataRow, WhereClause);
            Exception e = null;
            if (DataRow.RowState != DataRowState.Deleted)
                Delete(DataRow.Table.TableName, WhereClause.ToString());
            if (e == null)
                DataRow.Delete();
            return e;
        }

        /// <summary>
        /// Return the number of rows effected.
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="WhereClause"></param>
        /// <returns></returns>
        public int Delete(string TableName, string WhereClause)
        {
            StringBuilder delete = new StringBuilder();
            delete.Append("DELETE FROM ");
            delete.Append(TableName);
            delete.Append(" WHERE ");
            delete.Append(WhereClause);
            return Delete(delete);
        }

        #region DeleteTable
        public Exception DeleteTable(DataTable Table)
        {
            Exception e = null;
            if (Table == null)
                return e;
            for (int i = Table.Rows.Count - 1; i > -1; i--)
            {
                e = Delete(Table.Rows[i]);
                if (e != null)
                    return e;
            }
            return e;
        }
        #endregion
        #endregion

        #region Scalar
        public object Scalar(StringBuilder query)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd = conn.CreateCommand();
            cmd.CommandText = query.ToString();
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                LogError(e.Message, query);
                throw e;
            }
            finally
            {
				if (conn.State == ConnectionState.Open)
					conn.Close();
            }
        }

        public object Scalar(string Item, string TableName, string WhereClause)
        {
            StringBuilder scalar = new StringBuilder();
            scalar.Append("SELECT #ITEM FROM #TABLE WHERE #WHERE; ");
            scalar.Replace("#ITEM", Item);
            scalar.Replace("#TABLE", TableName);
            scalar.Replace("#WHERE", WhereClause);
            return Scalar(scalar);
        }
        #endregion


        #region CreateTransaction
        public Transaction CreateTransaction()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            return new Transaction(conn.BeginTransaction());
        }
        #endregion       


        #region Procedure
        public bool Procedure(string name, params SqlParameter[] parms)
        {
            return Procedure(name, null, parms);
        }

        public bool Procedure(string name, Transaction transaction, params SqlParameter[] parms)
        {
            bool ret = true;
            if (string.IsNullOrEmpty(name))
                throw new NotSupportedException("A value must be specified for the stored procedure name.");

            SqlConnection conn;
            if (transaction == null || transaction.SqlTransaction==null || transaction.SqlTransaction.Connection == null)
                conn = new SqlConnection(ConnectionString);
            else
                conn = transaction.SqlTransaction.Connection;

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = name;
			cmd.CommandTimeout = 0;
            if (transaction != null && transaction.SqlTransaction != null)
                cmd.Transaction = transaction.SqlTransaction;     
            

            if (parms != null)
            {
                for (int i = 0; i < parms.Length; i++)
                {
                    System.Data.SqlClient.SqlParameter tempPar = new System.Data.SqlClient.SqlParameter();
                    tempPar.Direction = (ParameterDirection)parms[i].Direction;
                    tempPar.ParameterName = parms[i].ParameterName;
                    tempPar.Value = parms[i].Value;

                    cmd.Parameters.Add(tempPar);
                }
            }

            try
            {
                if(conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.ExecuteNonQuery();
                if (parms != null)
                {
                    for (int i = 0; i < cmd.Parameters.Count; i++)
                    {
                        System.Data.SqlClient.SqlParameter tempPar = cmd.Parameters[i];
                        parms[i].Value = tempPar.Value;
                    }
                }
            }
            catch (Exception e)
            {
                LogError(e.Message, new StringBuilder("Stored Procedure: " + name));
                throw e;
            }
            finally
            {
                if (transaction==null&&conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return ret;
        }
        #endregion


        #region SaveRow
        public int SaveRow(DataRow row)
        {
            int NumberOfRowsEffected = Update(BuildSaveString(row));
            row.AcceptChanges();
            return NumberOfRowsEffected;
        }
        #endregion


        #region SaveTable
        public int SaveTable(DataTable Table)
        {
            ScriptBatch save = new ScriptBatch(this);
            for (int i = Table.Rows.Count - 1; i > -1; i--)
            {
                int NumberOfRowsEffected = 0;
                NumberOfRowsEffected = save.Append(BuildSaveString(Table.Rows[i]));
                if (NumberOfRowsEffected < 1)
                    return NumberOfRowsEffected;
                Table.Rows[i].AcceptChanges();
            }
            return save.Flush();
        }
        #endregion

        #region BuildSaveString
        public StringBuilder BuildSaveString(DataRow row)
        {
            DataTable table = row.Table;
            string TableName = table.TableName;
            StringBuilder query = new StringBuilder();
            switch (row.RowState)
            {
                case DataRowState.Added:
                    query.Append("INSERT INTO #TN (#CO) VALUES (#VA);");
                    query.Replace("#TN", TableName);
                    BuildColsAndVals(row, query);
                    break;
                case DataRowState.Deleted:
                    row.RejectChanges();
                    query.Append("DELETE FROM #TN WHERE #WH;");
                    query.Replace("#TN", TableName);
                    BuildWhereString(row, query);
                    row.Delete();
                    break;
                case DataRowState.Modified:
                    query.Append("UPDATE #TN SET #SE WHERE #WH;");
                    query.Replace("#TN", TableName);
                    BuildWhereString(row, query);
                    if (!BuildUpdateSet(row, query))
                        return new StringBuilder("");
                    break;
                default:
                    break;
            }
            return query;
        }
        #endregion

        #region BuildWhereString
        public void BuildWhereString(DataRow row, StringBuilder query)
        {
            DataTable table = row.Table;
            DataColumn[] keys = table.PrimaryKey;
            StringBuilder W = new StringBuilder();
            for (int i = 0; i < keys.Length; i++)
            {
                if (i > 0)
                    W.Append(" AND ");
                W.Append("#NA=#VA");
                DataColumn col = keys[i];
                W.Replace("#NA", col.ColumnName);
                W.Replace("#VA", Convert(row[col]));
            }
            query.Replace("#WH", W.ToString());
        }
        #endregion

        #region BuildColsAndVals - For Insert
        public void BuildColsAndVals(DataRow row, StringBuilder insert)
        {
            DataTable table = row.Table;
            for (int i = 0; i < table.Columns.Count; i++)
            {
                DataColumn col = table.Columns[i];
                if (col.ReadOnly)
                    continue;
                insert.Replace("#CO", "[#OC],#CO");
                insert.Replace("#VA", "#AV,#VA");
                insert.Replace("#OC", col.ColumnName);
                insert.Replace("#AV", Convert(row[col]));
            }
            insert.Replace(",#CO", "");
            insert.Replace(",#VA", "");
        }
        #endregion

        #region BuildUpdateSet - For Update
        /// <summary>
        /// 
        /// </summary>
        /// <param name="row">The Data row</param>
        /// <param name="update">The update StringBuilder containing 'SET #SE' indicating the position to insert the SET Clause.</param>
        /// <returns>True if at least one column is going to be modified.</returns>
        public bool BuildUpdateSet(DataRow row, StringBuilder update)
        {
            DataTable table = row.Table;
            DataColumn[] keys = table.PrimaryKey;
            bool insertComma = false;
            for (int i = 0; i < table.Columns.Count; i++)
            {
                DataColumn col = table.Columns[i];
                if (col.ReadOnly || row[col, DataRowVersion.Original].ToString() == row[col, DataRowVersion.Current].ToString())
                    continue;
                if (insertComma)
                {
                    update.Replace("#SE", ",#SE");
                }
                update.Replace("#SE", "[#CO]=#VA#SE");
                update.Replace("#CO", col.ColumnName);
                update.Replace("#VA", Convert(row[col]));
                insertComma = true;
            }
            update.Replace("#SE", "");
            return insertComma;
        }
        #endregion

        #region Convert
        /// <summary>
        /// Converts a C# DataType to an SqlString.
        /// </summary>
        /// <param name="item">The item to convert</param>
        /// <returns>The string in quotes if needed.</returns>
        public static string Convert(object item)
        {
			return Sql.Convert(item);
        }
        #endregion
        #endregion

        #region GetServerIPFromConnectionString
        /// <summary>
        /// Parses a SQL Server connection string and returns the server name.
        /// 
        /// Example:
        /// GetSqlServerFromConnectionString("server=10.50.30.30;uid=etc_webadmin;pwd=adminisgreat;database=ETCQA")
        /// 
        /// Returns:
        /// "10.50.30.30"
        /// </summary>
        /// <param name="connectionString">The full connection string.</param>
        /// <returns>The IP address or server name embedded in the string.  If we cannot determine this value, then it returns an empty string.</returns>
        public static string GetServerIPFromConnectionString(string connectionString)
        {
            string retVal = "";
            int charIndex = 0;
            int tokenLength = 0;

            connectionString = connectionString.ToLower();
            string ServerToken = "server=";
            if (connectionString.IndexOf("server=") > -1)
                ServerToken = "server=";
            else if (connectionString.IndexOf("data source=")>-1)
                ServerToken = "data source=";


            const string ServerDelimiter = ";";

            tokenLength = ServerToken.Length;

            // --------------------------------------------------
            // Look for the string "server="

            charIndex = connectionString.IndexOf(ServerToken);
            if (charIndex > -1)
            {
                // If the string is present, then remove everything before the string (inclusive)
                retVal = connectionString.Substring(charIndex + tokenLength);

                // Look for the terminating character ";"
                charIndex = retVal.IndexOf(ServerDelimiter);

                if (charIndex > -1)
                {
                    // If the terminating character is there, then remove everything after it (inclusive)
                    retVal = retVal.Substring(0, charIndex);
                }
            }

            // --------------------------------------------------

            return retVal;
        }
        #endregion

        #region InitialCatalog
        public string InitialCatalog
        {
            get
            {
                string retVal = "";
                int charIndex = 0;
                int tokenLength = 0;

                connectionString = connectionString.ToLower();
                string ServerToken = "server=";
                if (connectionString.IndexOf("server=") > -1)
                    ServerToken = "server=";
                else if (connectionString.IndexOf("initial catalog=") > -1)
                    ServerToken = "initial catalog=";


                const string ServerDelimiter = ";";

                tokenLength = ServerToken.Length;

                // --------------------------------------------------
                // Look for the string "server="

                charIndex = connectionString.IndexOf(ServerToken);
                if (charIndex > -1)
                {
                    // If the string is present, then remove everything before the string (inclusive)
                    retVal = connectionString.Substring(charIndex + tokenLength);

                    // Look for the terminating character ";"
                    charIndex = retVal.IndexOf(ServerDelimiter);

                    if (charIndex > -1)
                    {
                        // If the terminating character is there, then remove everything after it (inclusive)
                        retVal = retVal.Substring(0, charIndex);
                    }
                }

                // --------------------------------------------------

                return retVal;
            }
        }
        #endregion

        public string ServerName
        {
            get
            {
                return GetServerIPFromConnectionString(this.ConnectionString);
            }
        }

        #region TestConnection
        public bool TestConnection()
        {
            StringBuilder query = new StringBuilder("SELECT @@Version;");
            object o = Scalar(query);
            if (o is string)
                return true;
            return false;
        }
        #endregion


		public static T[] DataReaderToTypeList<T>(SqlDataReader dr)
		{
			var list = new List<T>();
			var t = typeof(T);

			var fields = t.GetFields();
			var props = t.GetProperties();

			while (dr.Read())
			{
				var o = (T)Activator.CreateInstance(t);
				if (fields != null)
				{
					foreach (var fi in fields)
					{
						var fda = DataAttribute.GetMemberInfoDataAttribute(fi, null);
						if (fda == null || !fda.Get)
							continue;
						var cn = fda.GetColumn(fi);
						try
						{
							var val = dr[cn];
							if (val == null || val is DBNull)
								continue;
							if (val.GetType() != Reflection.Type.GetNormalizedType(fi.FieldType))
								throw new Exception("The Type of value for column '" + fda.GetColumn(fi) + "' does not match.");
							fi.SetValue(o, val);
						}
						catch (IndexOutOfRangeException)
						{
							throw new Exception("[Data] field '" + cn + "' on object '" + t.Name + "' not found in query result.");
						}
					}
				}
				if (props != null)
				{
					foreach (var p in props)
					{
						var pda = DataAttribute.GetMemberInfoDataAttribute(p, null);
						if (pda == null || !pda.Get)
							continue;
						var cn = pda.GetColumn(p);
						try
						{
							var val = dr[cn];
							if (val == null || val is DBNull)
								continue;
							if (val.GetType() != Reflection.Type.GetNormalizedType(p.PropertyType))
								throw new Exception("The Type of value for column '" + pda.GetColumn(p) + "' does not match.");
							p.SetValue(o, val, null);
						}
						catch (IndexOutOfRangeException)
						{
							throw new Exception("[Data] property '" + cn + "' on object '" + t.Name + "' not found in query result.");
						}
					}
				}
				list.Add(o);
			}
			return list.ToArray();
		}
		public class ProcedureCall
		{
			public string Name;
			public System.Data.SqlClient.SqlParameter[] Parameters;

			public static ProcedureCall Get(string name)
			{
				return new ProcedureCall() { Name = name };
			}
			public ProcedureCall p(string name, object value, bool output=false)
			{
				var l = new List<System.Data.SqlClient.SqlParameter>() { };
				if (Parameters != null)
					l.AddRange(Parameters);
				var p = new System.Data.SqlClient.SqlParameter(name, value);
				if(output)
					p.Direction = ParameterDirection.Output;
				l.Add(p);
				Parameters = l.ToArray();
				return this;
			}
			public object GetValue(string parameterName)
			{
				if (string.IsNullOrEmpty(parameterName) || Parameters == null)
					return null;
				return Parameters.Where(p => p.ParameterName == parameterName).Select(p => p.Value).FirstOrDefault();
			}
		}
		public T[] Get<T>(ProcedureCall p)
		{
			using (var connection = new SqlConnection(ConnectionString))
			{
				var cmd = new SqlCommand(p.Name, connection);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.AddRange(p.Parameters);
				connection.Open();
				using (var dr = cmd.ExecuteReader())
				{
					return DataReaderToTypeList<T>(dr);
				}
			}
		}
		public T[] Get<T>(StringBuilder sql)
		{
			using (var connection = new SqlConnection( ConnectionString))
			{
				var cmd = new SqlCommand(sql.ToString(), connection);
				connection.Open();
				using (var dr = cmd.ExecuteReader())
				{
					return DataReaderToTypeList<T>(dr);
				}
			}
		}		
		public T[] Get<T>(Hashtable andClause)
		{
			var t=typeof(T);
			var da = DataAttribute.GetByType(t);
			var table=da.GetTable(t);
			var sql = new StringBuilder();
			sql.Append(Sql.SelectSql(table, andClause));
			return Get<T>(sql);
		}
		public T[] Get<T>(string where)
		{
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);
			var sql = new StringBuilder();
			sql.Append(Sql.SelectSql(table, where));
			return Get<T>(sql);
		}
		public T[] Get<T>(object clause)
		{
			return Get<T>(HashtableExtensions.Convert(clause));
		}


		public delegate bool Clause<T>(T obj);

		public T[] Get<T>(System.Linq.Expressions.Expression<Clause<T>> e)
		{
			var w = ClauseTranslator.Translate(e);
			return Get<T>(w);
		}
		public T GetOne<T>(System.Linq.Expressions.Expression<Clause<T>> e)
		{
			var l=Get<T>(e);
			if(l!=null&&l.Length==1)
				return l[0];
			return default(T);
		}


		public T GetOne<T>(object idOrClause)
		{
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			if (idOrClause == null)
				return default(T);
			var idOrClauseType = idOrClause.GetType();
			T[] l = null;
			if (!idOrClauseType.IsClass || idOrClauseType == typeof(string))
			{
				var h = new Hashtable();
				h[da.IdColumn] = idOrClause;
				l = Get<T>(h);
			}
			else
				l = Get<T>(HashtableExtensions.Convert(idOrClause));
			if (l != null && l.Length == 1)
				return l[0];
			return default(T);
		}
		public T[] GetAll<T>()
		{
			return Get<T>(null as System.Collections.Hashtable);
		}	
		


		public static System.Collections.Hashtable GetValues(object o, FieldInfo[] fields, PropertyInfo[] props, Type ColumnAttribute)
		{
			var h = new System.Collections.Hashtable();
			if (fields != null)
			{
				foreach (var fi in fields)
				{
					var fda = DataAttribute.GetMemberInfoDataAttribute(fi, ColumnAttribute);
					if (fda == null || fda.IdColumn == fi.Name)
						continue;

					h[fda.GetColumn(fi)] = fi.GetValue(o);
				}
			}
			if (props != null)
			{
				foreach (var p in props)
				{
					var pda = DataAttribute.GetMemberInfoDataAttribute(p, ColumnAttribute);
					if (pda == null || pda.IdColumn == p.Name)
						continue;
					h[pda.GetColumn(p)] = p.GetValue(o, null);
				}
			}
			return h;
		}

		public System.Collections.Hashtable ClauseToHashtable(object clause, DataAttribute.ColumnName[] clausableColumns, DataAttribute da)
		{
			if (clause == null)
				return null;
			var h = new System.Collections.Hashtable();
			if (clause.GetType().IsClass)
				h = HashtableExtensions.Convert(clause, clausableColumns);
			else
			{
				if (da == null)
					da = new DataAttribute();
				if (da.HasCompositeId)
					throw new Exception("Table has Composite Id, but only one value was supplied.");
				h[da.IdColumn] = clause;				
			}
			return h;
		}

		public void Update<T>(IdentifiedObject[] l, int? flushevery=null)
		{
			if (l == null)
				return;
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);

			RunScript<IdentifiedObject>(l, flushevery, delegate(IdentifiedObject i)
			{
				var clausableColumns = da.GetClausableColumns(t);
				var updatableColumns = da.GetUpdatableColumns(t);
				return Sql.UpdateSql(table, HashtableExtensions.Convert(i.Values, updatableColumns), ClauseToHashtable(i.Clause, clausableColumns, da));
			});
		}
		public void Update<T>(object clause, object values)  
		{
			if(clause==null||values==null)
				return;
			Update<T>(new IdentifiedObject[] { new IdentifiedObject() { Clause = clause, Values = values } }, null);
		}

		public void Insert<T>(IdentifiedObject[] l, int? flushevery = null) 
		{
			if (l == null)
				return;
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);

			RunScript<IdentifiedObject>(l, flushevery, delegate(IdentifiedObject i)
			{
				var clausableColumns = da.GetClausableColumns(t);
				var updatableColumns = da.GetInsertableColumns(t);
				return Sql.InsertSql(table, HashtableExtensions.Convert(i.Values, updatableColumns), HashtableExtensions.Convert(i.Clause, clausableColumns), da.IdColumnInsertable);
			});
		}
		public void Insert<T>(object values, object clause = null)
		{
			Insert<T>(new IdentifiedObject[] { new IdentifiedObject() { Clause = clause, Values = values } }, null);
		}
				

		

		public void Save<T>(IdentifiedObject[] l, int? flushevery = null)
		{
			if (l == null)
				return;
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);

			ScriptAppender<IdentifiedObject> sa = delegate(IdentifiedObject i)
			{

				var clausableColumns = da.GetClausableColumns(t);
				var updatableColumns = da.GetUpdatableColumns(t);
				var insertableColumns = da.GetInsertableColumns(t);

				System.Collections.Hashtable clause = HashtableExtensions.Convert(i.Clause, clausableColumns);
				bool idColumnInsertable = da.IdColumnInsertable;

				if (!da.HasCompositeId && (Json.ConvertToJson(clause[da.IdColumn]) == "0" || Json.ConvertToJson(clause[da.IdColumn]) == "null"))
				{
					clause = null;
					idColumnInsertable = false;
					insertableColumns = updatableColumns;
				}

				return Sql.SaveSql(table, HashtableExtensions.Convert(i.Values, insertableColumns), HashtableExtensions.Convert(i.Values, updatableColumns), clause , idColumnInsertable);
			};

			if (l.Length == 1)
			{
				var sql = new StringBuilder();
				sql.Append(sa(l[0]));
				sql.Append(@"
select SCOPE_IDENTITY();");
				var id=Scalar(sql);
				if (!da.HasCompositeId && id!=null && !(id is DBNull)) //check for single id column and try to set to id;
				{
					var mi = Reflection.MemberInfo.GetMemberInfo(t, da.IdColumn);
					var mit = Reflection.MemberInfo.GetMemberInfoType(mi);
					object val = null;

					if (mit == typeof(byte) || mit==typeof(byte?))
						val = byte.Parse(id.ToString());
					if (mit == typeof(short) || mit == typeof(short?))
						val = short.Parse(id.ToString());
					if (mit == typeof(int) || mit == typeof(int?))
						val = int.Parse(id.ToString());
					if (mit == typeof(long) || mit == typeof(long?))
						val = long.Parse(id.ToString());
					if (mit == typeof(string))
						val = id.ToString();

					Reflection.MemberInfo.SetMemberInfoValue(mi, l[0].Values, val);

				}
			}
			else
				RunScript<IdentifiedObject>(l, flushevery, sa);
		}
		public void Save<T>(object values, object clause = null)
		{
			Insert<T>(new IdentifiedObject[] { new IdentifiedObject(){ Clause=clause, Values=values} }, null);
		}
		public void Save<T>(T[] l, int? flushevery=null)
		{
			if (l == null)
				return;
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);

			var fields = t.GetFields();
			var props = t.GetProperties();

			var idl = new List<IdentifiedObject>();
			foreach (var o in l)
			{
				var io = new IdentifiedObject() { Values = o };

				var clause = new System.Collections.Hashtable();
				foreach (var idcol in da.IdColumns)
				{
					var idprop = t.GetProperty(idcol);
					object id = null;
					if (idprop != null)
					{
						id = idprop.GetValue(o, null);
					}
					else
					{
						var idfield = t.GetField(idcol);
						if (idfield == null)
							throw new Exception("No Id (" + idcol + ") field or property on this type.");
						id = idfield.GetValue(o);
					}
					clause[idcol] = id;
				}
				
				io.Clause = clause;

				idl.Add(io);
			}
			Save<T>(idl.ToArray(), flushevery);
		}

		public void Save<T>(T obj)
		{
			Save<T>(new T[] { obj });
		}


		public void Delete<T>(Hashtable[] ids)
		{
			if (ids == null||ids.Length==0)
				return;
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);

			var sql=new StringBuilder();

			foreach(var id in ids)
			{		
				sql.Append(Sql.DeleteSql(table, id));
			}
			RunScript(sql);
		}

		public void Delete<T>(Hashtable h)
		{
			Delete<T>(new Hashtable[] { h });
		}

		public void Delete<T>(object idOrClause)
		{
			if (idOrClause == null)
				return;
			var idOrClauseType=idOrClause.GetType();
			if (!idOrClauseType.IsClass || idOrClauseType == typeof(string))
			{
				var h = new Hashtable();
				h["Id"] = idOrClause;
				Delete<T>(h);
			}
			else
				Delete<T>(HashtableExtensions.Convert(idOrClause));
		}

		public void Delete<T>(string[] ids)
		{
			if (ids == null || ids.Length == 0)
				return;
			var hids = new List<Hashtable>();
			foreach (var id in ids)
			{
				var hid = new Hashtable();
				hid["Id"] = long.Parse(id);
				hids.Add(hid);
			}
			Delete<T>(hids.ToArray());
		}


		public delegate string ScriptAppender<T>(T o);
		public static int DefaultFlushCount = 25;

		public void RunScript<T>(T[] l, int? flushcount, ScriptAppender<T> a)
		{
			if (l == null)
				return;
			int c = 0;
			var sql = new StringBuilder();
			foreach (var o in l)
			{
				sql.Append(a(o));
				c++;
				if (l.Length > 1 && c > (flushcount ?? DefaultFlushCount))
				{
					RunScript(sql);
					sql.Clear();
					c = 0;
				}
			}		
			if(c>0)
				RunScript(sql);
		}
				

		public static DataTable ConvertToDataTable<T>(T[] l)
		{
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);

			var dt = new DataTable();
			var cl=da.GetInsertableColumns(t);

			var ml = new List<Web.Ajax.Reflection.MemberInfo>();

			cl.Each(c => {
				var mi = new Reflection.MemberInfo(c.SqlName, typeof(T));
				ml.Add(mi);
				dt.Columns.Add(mi.Name, mi.GetMemberType());
			});


			if(l==null)
				return dt;
			l.Each(o => {
				var r = dt.NewRow();
				ml.Each(m =>
				{
					r[m.Name] = m.GetValue(o);
				});
				dt.Rows.Add(r);
				//dt.AcceptChanges();
			});

			return dt;
		}

		public static int DefaultBulkBatchSize = 1000;
		public void BulkInsert(string table, DataTable dt, int? batchSize=null)
		{
			var m = "Failed to Bulk Insert. ";
			if (string.IsNullOrWhiteSpace(table))
				throw new Exception(m+"No table name supplied.");
			if (dt == null)
				return;
			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();
				using(var bulkCopy=new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
				{
					bulkCopy.DestinationTableName = table;
					bulkCopy.BatchSize = batchSize ?? DefaultBulkBatchSize;

					foreach (DataColumn c in dt.Columns)
						bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);

					bulkCopy.WriteToServer(dt);
				}
			}
		}

		public void BulkInsert<T>(T[] l, int? batchSize=null)
		{
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);
			var dt = ConvertToDataTable<T>(l);
			BulkInsert(table, dt, batchSize);
		}

		public void Truncate<T>()
		{
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);
			if (string.IsNullOrWhiteSpace(table))
				return;
			RunScript(Sql.TruncateSql(table));
		}

		public void UpdateStatistics<T>()
		{
			var t = typeof(T);
			var da = DataAttribute.GetByType(t);
			var table = da.GetTable(t);
			if (string.IsNullOrWhiteSpace(table))
				return;
			RunScript(Sql.UpdateStatisticsSql(table));
		}
	}

    #region Parameter Class
    public class SqlParameter
    {
        private string parameterName;
        private object value;
        private ParameterDirection direction;

        public SqlParameter(string parameterName, object value)
        {
            this.parameterName = parameterName;
            this.direction = ParameterDirection.Input;
            this.value = value;
        }

        public SqlParameter(string parameterName, object value, ParameterDirection direction)
        {
            this.parameterName = parameterName;
            this.direction = direction;
            this.value = value;
        }

        public string ParameterName
        {
            get
            {
                return parameterName;
            }
            set
            {
                parameterName = value;
            }
        }

        public ParameterDirection Direction
        {
            get
            {
                return direction;
            }
            set
            {
                direction = value;
            }
        }

        public object Value
        {
            get
            {
                return value;
            }
            set
            {
                this.value = value;
            }
        }

    }

    #endregion

    #region Transaction Class
    public class Transaction : IDisposable
    {
        public SqlTransaction SqlTransaction;

        public Transaction(SqlTransaction SqlTransaction)
        {
            this.SqlTransaction = SqlTransaction;
        }

        public void Commit()
        {
            if (SqlTransaction != null)
                SqlTransaction.Commit();
        }

        public void Rollback()
        {
            if (SqlTransaction != null)
                SqlTransaction.Rollback();
        }

        void IDisposable.Dispose()
        {
            if (SqlTransaction != null)
            {
                if (SqlTransaction.Connection != null && SqlTransaction.Connection.State == ConnectionState.Open)
                    SqlTransaction.Connection.Close();
            }
        }        
    }
    /*
     //To use Transaction:
     
     using (Transaction t=db.CreateTransaction())
     {
        db.Procedure("procname", t, params);
        t.Commit();
     } 
     */
    #endregion

}
